﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Unit2_Session3_Detail : System.Web.UI.Page
{
    private OleDbConnection connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["HotelsConnectionString"].ToString());



    protected void Page_Load(object sender, EventArgs e)
    {
        if (!(Page.IsPostBack))
        {

            AddHotelsView();
            CheckLiked();
            GetAverageRating();
        }
        string hotelID = Request.QueryString["ID"];
        if (User.IsInRole("superuser"))
        {
            plcUsers.Visible = true;
        }
        else
        {
            plcUsers.Visible = false;
        }
        if (!(string.IsNullOrEmpty(hotelID)))
        {
            if (IsPostBack == false)
            {
                SelectComments();
                SelectTags();
            }
        }
        else
        {
            Response.Redirect("Unit2-Session3.aspx");
        }

    }

    private void GetAverageRating()
    {
        string avgRatingSql = "SELECT HotelID, AVG(Rating) AS AvgRating FROM Rating WHERE HotelID = @f1 GROUP BY HotelID";


        //Show the inserted tags in the page for that hotel
        OleDbCommand avgRatingCmd = new OleDbCommand(avgRatingSql, connection);
        avgRatingCmd.CommandType = System.Data.CommandType.Text;
        avgRatingCmd.Parameters.AddWithValue("@f1", Request.QueryString["ID"]);
        if (connection.State == System.Data.ConnectionState.Closed)
        {
            connection.Open();
        }
        OleDbDataReader reader = avgRatingCmd.ExecuteReader();
        if (reader.HasRows)
        {
            reader.Read();
            double avgRate = (double)reader.GetValue(1);
            this.AverageRating.CurrentRating = Convert.ToInt16(Math.Round(avgRate));
        }

    }

    private void CheckLiked()
    {
        string likeSelectSql = "SELECT * FROM [Like] WHERE HotelID = @f1 AND UserName=@f2";
        //Show the inserted tags in the page for that hotel
        OleDbCommand likeSelectCmd = new OleDbCommand(likeSelectSql, connection);
        likeSelectCmd.CommandType = System.Data.CommandType.Text;
        likeSelectCmd.Parameters.AddWithValue("@f1", Request.QueryString["ID"]);
        likeSelectCmd.Parameters.AddWithValue("@f2", User.Identity.Name);
        if (connection.State == System.Data.ConnectionState.Closed)
        {
            connection.Open();
        }
        OleDbDataReader reader = likeSelectCmd.ExecuteReader();
        if (reader.HasRows)
        {
            this.btnLike.Enabled = false;
        }
    }

  
    private void SelectTags()
    {
        string tagSelectSql = "SELECT Tag FROM TagsByHotelIDView WHERE ID = @f1";
        //Show the inserted tags in the page for that hotel
        OleDbCommand tagSelectCmd = new OleDbCommand(tagSelectSql, connection);
        tagSelectCmd.CommandType = System.Data.CommandType.Text;
        tagSelectCmd.Parameters.AddWithValue("@f1", Request.QueryString["ID"]);
        if (connection.State == System.Data.ConnectionState.Closed)
        {
            connection.Open();
        }
        this.grdTags.DataSource = tagSelectCmd.ExecuteReader();
        this.grdTags.DataBind();
    }

    private void SelectComments()
    {
        string sql = "SELECT Author, Title, Comment FROM Comments WHERE HotelID = @f1";
        //OleDbConnection connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["HotelsConnectionString"].ToString());
        OleDbCommand cmdComments = new OleDbCommand(sql, connection);
        cmdComments.CommandType = System.Data.CommandType.Text;
        cmdComments.Parameters.AddWithValue("@f1", Request.QueryString["ID"]);
        if (connection.State == System.Data.ConnectionState.Closed)
        {
            connection.Open();
        }
        this.grdComments.DataSource = cmdComments.ExecuteReader();
        this.grdComments.DataBind();
    }
    private void AddHotelsView()
    {
        string hotelsViewedSql = "INSERT INTO HotelsViewed(Traveller, HotelID) VALUES(@f1, @f2)";
        OleDbCommand hotelViewCmd = new OleDbCommand(hotelsViewedSql, connection);
        hotelViewCmd.CommandType = System.Data.CommandType.Text;
        hotelViewCmd.Parameters.AddWithValue("@f1", User.Identity.Name);
        hotelViewCmd.Parameters.AddWithValue("@f2", Request.QueryString["ID"]);
        if (connection.State == System.Data.ConnectionState.Closed)
        {
            connection.Open();
        }
        hotelViewCmd.ExecuteNonQuery();
    }


    protected void btnAddNewComment_Click(object sender, EventArgs e)
    {
        string title = this.txtTitle.Text;
        string author = User.Identity.Name;
        string comment = this.txtComment.Text;

        string sql = "INSERT INTO Comments(Author, Title, Comment, HotelID) Values(@f1, @f2, @f3, @f4)";
        //OleDbConnection connection = new OleDbConnection(ConfigurationManager.ConnectionStrings["HotelsConnectionString"].ToString());

        OleDbCommand cmd = new OleDbCommand(sql, connection);
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.AddWithValue("@f1", author);
        cmd.Parameters.AddWithValue("@f2", title);
        cmd.Parameters.AddWithValue("@f3", comment);
        cmd.Parameters.AddWithValue("@f4", Request.QueryString["ID"]);
        if (connection.State == System.Data.ConnectionState.Closed)
        {
            connection.Open();
        }
        cmd.ExecuteNonQuery();

      
        SelectComments();
    }
    protected void btnAddNewTags_Click(object sender, EventArgs e)
    {
        string[] tags = this.txtNewTags.Text.Split(',');
        string tagInsertSql = "INSERT INTO Tags(Tag) VALUES(@f1)";

        if (txtNewTags.Text != string.Empty)
        {
            foreach (string tag in tags)
            {
                OleDbCommand tagInsertCmd = new OleDbCommand(tagInsertSql, connection);
                tagInsertCmd.CommandType = System.Data.CommandType.Text;
                tagInsertCmd.Parameters.AddWithValue("@f1", tag);
                if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                tagInsertCmd.ExecuteNonQuery();

                //Get the last insertion tag id
                OleDbCommand lastTagCmd = new OleDbCommand("SELECT TOP 1 ID FROM Tags ORDER BY ID DESC", connection);
                lastTagCmd.CommandType = System.Data.CommandType.Text;

                if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                OleDbDataReader reader = lastTagCmd.ExecuteReader();
                reader.Read();
                string newTagID = (string)reader.GetValue(0);

                InsertTagIdToTagHotelTable(newTagID);
            }
        }
        else
        {
            foreach (ListItem existingTag in this.chkTags.Items)
            {
                if (existingTag.Selected)
                {
                    InsertTagIdToTagHotelTable(existingTag.Value);
                }
            }
        }

        SelectTags();

    }

    private void InsertTagIdToTagHotelTable( string newTagID)
    {
        string tagHotelInsertSql = "INSERT INTO HotelTag(HotelID, TagID) VALUES(@f1, @f2)";
        int tagID = Convert.ToInt16(newTagID);
        //Make the join in the HotelTag table
        OleDbCommand tagHotelInsertCmd = new OleDbCommand(tagHotelInsertSql, connection);
        tagHotelInsertCmd.CommandType = System.Data.CommandType.Text;
        tagHotelInsertCmd.Parameters.AddWithValue("@f1", Request.QueryString["ID"]);
        tagHotelInsertCmd.Parameters.AddWithValue("@f2", tagID);
        if (connection.State == System.Data.ConnectionState.Closed)
        {
            connection.Open();
        }
        tagHotelInsertCmd.ExecuteNonQuery();
    }
    protected void Rating1_Changed(object sender, AjaxControlToolkit.RatingEventArgs e)
    {
        string ratingHotelDeleteSql = "DELETE FROM Rating WHERE (HotelID=@f1 AND UserName=@f2)";
        //Make the join in the HotelTag table
        OleDbCommand ratingHotelDeleteCmd = new OleDbCommand(ratingHotelDeleteSql, connection);
        ratingHotelDeleteCmd.CommandType = System.Data.CommandType.Text;
        ratingHotelDeleteCmd.Parameters.AddWithValue("@f1", Request.QueryString["ID"]);
        ratingHotelDeleteCmd.Parameters.AddWithValue("@f2", User.Identity.Name);
        if (connection.State == System.Data.ConnectionState.Closed)
        {
            connection.Open();
        }
        ratingHotelDeleteCmd.ExecuteNonQuery();

        string ratingHotelInsertSql = "INSERT INTO Rating(Rating, HotelID, UserName) VALUES(@f1, @f2, @f3)";
        //Make the join in the HotelTag table
        OleDbCommand ratingHotelInsertCmd = new OleDbCommand(ratingHotelInsertSql, connection);
        ratingHotelInsertCmd.CommandType = System.Data.CommandType.Text;
        ratingHotelInsertCmd.Parameters.AddWithValue("@f1", e.Value);
        ratingHotelInsertCmd.Parameters.AddWithValue("@f2", Request.QueryString["ID"]);
        ratingHotelInsertCmd.Parameters.AddWithValue("@f3", User.Identity.Name);
        if (connection.State == System.Data.ConnectionState.Closed)
        {
            connection.Open();
        }
        ratingHotelInsertCmd.ExecuteNonQuery();
    }
    protected void btnLike_Click(object sender, EventArgs e)
    {
        string likeHotelInsertSql = "INSERT INTO [Like](HotelID, UserName) VALUES(@f1, @f2)";
        //Make the join in the HotelTag table
        OleDbCommand likeHotelInsertCmd = new OleDbCommand(likeHotelInsertSql, connection);
        likeHotelInsertCmd.CommandType = System.Data.CommandType.Text;
        likeHotelInsertCmd.Parameters.AddWithValue("@f1", Request.QueryString["ID"]);
        likeHotelInsertCmd.Parameters.AddWithValue("@f2", User.Identity.Name);
        if (connection.State == System.Data.ConnectionState.Closed)
        {
            connection.Open();
        }
        likeHotelInsertCmd.ExecuteNonQuery();
        this.btnLike.Enabled = false;
    }
}